import openpyxl
import datetime
import DBhelper

def addExcel():
        now = datetime.datetime.now()
        now_str = now.strftime("%Y-%M-%D %H:%M:%S")
        data = (1, "101", "天狗", "张三", "123456",
                "122-4567-8910", "123@163.com", 23, "男", "郑州",
                now_str, float(6000), "销售", "宏软")

        wb = openpyxl.load_workbook("excelTest.xlsx")
        if wb["2000行"] is None:
                ws = wb.create_sheet(title="2000行")
        else:
                ws = wb["2000行"]

        for i in range(2000):
                ws.append(data[0:10:])

        wb.save("excelTest.xlsx")
        wb.close()

# addExcel()

dbhelper = DBhelper.DBhelper('localhost','root','','hrjtpython')
sql = 'insert into user_info(id,idcard,username,realname,pwd,telphone,email,age,sex,address) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
wb = openpyxl.load_workbook("excelTest.xlsx")
ws = wb["2000行"]

for i in ws.iter_rows():
        list_data = []
        for j in i:
                list_data.append(j.value)
        dbhelper.update(sql,tuple(list_data))